from ris import db2 #library designed for SQL database connection and querying
from IPython.display import clear_output
import datetime
from datetime import date
import pandas as pd
import os
clear_output()
timestamp = datetime.datetime.now().strftime('%Y-%m-%d %H:%M')
print 'Notebook run: {}'.format(timestamp)
print os.getcwd()
# %load_ext sql
#Database connections
#gdb = db2.PostgresDb('dotdevpgsql02', 'GISGRID', quiet = True)
cdb = db2.PostgresDb('DOTDEVRHPGSQL01', 'CRASHDATA', quiet = True)
sdb = db2.PostgresDb('dotpgsql01', 'sip', quiet = True)
from sqlalchemy import create_engine
def df_to_sql(df,tbl_name,db):
engine = create_engine('postgresql://{user}:{pw}@10.243.154.88:5432/CRASHDATA'.format(user=db.params['user'],
pw=db.params['password']),
echo=False)
df.to_sql(name='{}'.format(tbl_name), con= engine, if_exists = 'replace', index=False)
db.query("""ALTER TABLE {tbl}
ALTER COLUMN geom TYPE Geometry USING geom::Geometry;
grant all on {tbl} to public;""".format(tbl=tbl_name))
return tbl_name
d7_corr = (db2.query_to_table(sdb, """
SELECT *
FROM public.sip_projects sp
join public.sip_projects_geo spg
on sp.pid=spg.pid_fk
where sip_year in (2019,2020)
and spg.nodeid=0
and sp.status = '11' """))
d7_itx = (db2.query_to_table(sdb, """
SELECT *
FROM public.sip_projects sp
join public.sip_projects_geo spg
on sp.pid=spg.pid_fk
where sip_year in (2019,2020)
and spg.segmentid=0
and sp.status = '11' """))
#d7_itx
#d7_corr
d7_pg_corr = df_to_sql(d7_corr,'d7_corr',cdb)
d7_pg_itx = df_to_sql(d7_itx,'d7_itx',cdb)
pjcts = (db2.query_to_table(cdb, """
select * from (
select distinct pid, pjct_name, sip_year, pm
from d7_itx itx
join (select * from districts_congressional where congdist = 7) d
on st_intersects(st_setsrid(itx.geom,2263), st_setsrid(d.geom,2263))
union
select distinct pid, pjct_name, sip_year, pm
from d7_corr corr
join (select * from districts_congressional where congdist = 7) d
on st_intersects(st_setsrid(corr.geom,2263), st_setsrid(d.geom,2263))
) x
"""))
pjcts.to_csv("District 7 Sip Projects-{}.csv".format(datetime.datetime.now().strftime('%Y-%m-%d')),index=False)
from IPython.display import Image
PATH = os.getcwd()
Image(filename = PATH + "\District 7 Sip Projects.png", width=1000, height=1000)
cdb.query( """DROP TABLE if exists public.d7_corr;
DROP TABLE if exists public.d7_itx;""")